VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "Form_Main"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Compare Database

' energy category type enum
Enum Energy_Cat_T
        ect_Heating = 1
        ect_Cooling = 2
        ect_Fans = 3
        ect_Pumps = 4
        ect_HeatRejection = 5
        ect_Refrigeration = 6
        ect_IntLighting = 7
        ect_ExtLighting = 8
        ect_IntEquipmentEle = 9
        ect_IntEquipmentGas = 10
        [ect_Num] = 10
        [ect_Hvac_Num] = 5      ' total hvac energy number
End Enum

' energy type (electricity or gas)
Enum Energy_T
        et_Electricity = 1
        et_Gas = 2
        [et_Num] = 2
End Enum

' the ACCESS table type
Enum Table_T
        tt_Wall = 1
        tt_Roof = 2
        tt_IntLighting = 3
        tt_IntEquipment = 4
        tt_ExtLighting = 5
        tt_Refrigeration = 6
        tt_Hvac = 7
        [tt_Num] = 7
        tt_Baseline = 8
        [tt_Sum] = 8
End Enum

' 2D energy output array definition
' the row is type of output table
' the column is energy category for each output table
Dim EnergyTable(1 To Table_T.tt_Num, 1 To Energy_Cat_T.ect_Num) As Double
' HVAC baseline energy data
Dim EnergyBaseline(1 To Energy_Cat_T.ect_Num) As Double
' final energy output data after processing
Dim EnergyOutput(1 To Energy_Cat_T.ect_Num) As Double
' energy type for each energy category
Dim EnergyType(1 To Energy_Cat_T.ect_Num) As Energy_T
' energy type string
Dim EnergyType_S(1 To Energy_T.et_Num) As String
' the total energy data for electricity and gas consumption
Dim TotalEnergyOutput(1 To Energy_Cat_T.ect_Num) As Double

' the previous energy output data
Dim EnergyOutput_Previous(1 To Energy_Cat_T.ect_Num) As Double
' the previous total energy data
Dim TotalEnergyOutput_Previous(1 To Energy_T.et_Num) As Double

' unit conversion from J to KWH
Const JtoKwh As Long = 3600000      ' 3E+6
Const BaselineTableName As String = "Baseline"

Enum Keyword_t
    C1 = 1
    C2 = 2
    C3 = 3
    C4 = 4
    C5 = 5
    C6 = 6
    C7 = 7
    C8 = 8
    C9 = 9
    D1 = 10
    D2 = 11
    D3 = 12
    D4 = 13
    E1 = 14
    E2 = 15
    E3 = 16
    E4 = 17
    E5 = 18
    E6 = 19
    E7 = 20
    F1 = 21
    G1 = 22
    G2 = 23
    G3 = 24
    G7 = 25
    H2 = 26
    KT_Num = 26     ' total number of keyword
End Enum

' the keyword string, hard coded.
Dim Keyword_S(1 To Keyword_t.KT_Num) As String
Dim Keyword_I(1 To Keyword_t.KT_Num) As Long               ' the keyword instance array obtained from input
Dim Table_Name(1 To Table_T.tt_Sum) As String                   ' the ACCESS table name
Dim Table_Header(1 To Energy_Cat_T.ect_Num) As String   ' each energy table's header applied to all tables


Private Sub initi()
    
    Keyword_S(Keyword_t.C1) = "C_1"
    Keyword_S(Keyword_t.C2) = "C_2"
    Keyword_S(Keyword_t.C3) = "C_3"
    Keyword_S(Keyword_t.C4) = "C_4"
    Keyword_S(Keyword_t.C5) = "C_5"
    Keyword_S(Keyword_t.C6) = "C_6"
    Keyword_S(Keyword_t.C7) = "C_7"
    Keyword_S(Keyword_t.C8) = "C_8"
    Keyword_S(Keyword_t.C9) = "C_9"
    Keyword_S(Keyword_t.D1) = "D_1"
    Keyword_S(Keyword_t.D2) = "D_2"
    Keyword_S(Keyword_t.D3) = "D_3"
    Keyword_S(Keyword_t.D4) = "D_4"
    Keyword_S(Keyword_t.E1) = "E_1"
    Keyword_S(Keyword_t.E2) = "E_2"
    Keyword_S(Keyword_t.E3) = "E_3"
    Keyword_S(Keyword_t.E4) = "E_4"
    Keyword_S(Keyword_t.E5) = "E_5"
    Keyword_S(Keyword_t.E6) = "E_6"
    Keyword_S(Keyword_t.E7) = "E_7"
    Keyword_S(Keyword_t.F1) = "F_1"
    Keyword_S(Keyword_t.G1) = "G_1"
    Keyword_S(Keyword_t.G2) = "G_2"
    Keyword_S(Keyword_t.G3) = "G_3"
    Keyword_S(Keyword_t.G7) = "G_7"
    Keyword_S(Keyword_t.H2) = "H_2"

    ' we initialize the energy type array
    EnergyType(Energy_Cat_T.ect_Heating) = et_Gas
    EnergyType(Energy_Cat_T.ect_Cooling) = et_Electricity
    EnergyType(Energy_Cat_T.ect_Fans) = et_Electricity
    EnergyType(Energy_Cat_T.ect_Pumps) = et_Electricity
    EnergyType(Energy_Cat_T.ect_HeatRejection) = et_Electricity
    EnergyType(Energy_Cat_T.ect_Refrigeration) = et_Electricity
    EnergyType(Energy_Cat_T.ect_IntLighting) = et_Electricity
    EnergyType(Energy_Cat_T.ect_ExtLighting) = et_Electricity
    EnergyType(Energy_Cat_T.ect_IntEquipmentEle) = et_Electricity
    EnergyType(Energy_Cat_T.ect_IntEquipmentGas) = et_Gas
    
    ' initialize table type array
    Table_Name(Table_T.tt_Wall) = "Envelope"
    Table_Name(Table_T.tt_Roof) = "Roof"
    Table_Name(Table_T.tt_IntLighting) = "IntLight"
    Table_Name(Table_T.tt_IntEquipment) = "IntEquipment"
    Table_Name(Table_T.tt_ExtLighting) = "ExtLight"
    Table_Name(Table_T.tt_Refrigeration) = "Refrigeration"
    Table_Name(Table_T.tt_Hvac) = "Hvac"
    Table_Name(Table_T.tt_Baseline) = "Baseline"
    
     'initialize table category array
    Table_Header(Energy_Cat_T.ect_Cooling) = "Cooling"
    Table_Header(Energy_Cat_T.ect_IntLighting) = "IntLight"
    Table_Header(Energy_Cat_T.ect_ExtLighting) = "ExtLight"
    Table_Header(Energy_Cat_T.ect_Fans) = "Fan"
    Table_Header(Energy_Cat_T.ect_Pumps) = "Pump"
    Table_Header(Energy_Cat_T.ect_HeatRejection) = "CoolingTower"
    Table_Header(Energy_Cat_T.ect_Refrigeration) = "Refrigeration"
    Table_Header(Energy_Cat_T.ect_IntEquipmentEle) = "IntEquipmentEle"
    Table_Header(Energy_Cat_T.ect_Heating) = "Heating"
    Table_Header(Energy_Cat_T.ect_IntEquipmentGas) = "IntEquipmentGas"
    
    ' energy type string
    EnergyType_S(Energy_T.et_Electricity) = "Electricity"
    EnergyType_S(Energy_T.et_Gas) = "Gas"

End Sub


' get instance ID for each input parameter
Private Sub getInputs()

    ' envelope inputs
    Keyword_I(Keyword_t.C1) = Me!EnvelopeInput_sfrm.Form.C1.Column(0)
    Keyword_I(Keyword_t.C2) = Me!EnvelopeInput_sfrm.Form.C2.Column(0)
    Keyword_I(Keyword_t.C3) = Me!EnvelopeInput_sfrm.Form.C3.Column(0)
    Keyword_I(Keyword_t.C4) = Me!EnvelopeInput_sfrm.Form.C4.Column(0)
    Keyword_I(Keyword_t.C5) = Me!EnvelopeInput_sfrm.Form.C5.Column(0)
    Keyword_I(Keyword_t.C6) = Me!EnvelopeInput_sfrm.Form.C6.Column(0)
    Keyword_I(Keyword_t.C7) = Me!EnvelopeInput_sfrm.Form.C7.Column(0)
    Keyword_I(Keyword_t.C8) = Me!EnvelopeInput_sfrm.Form.C8.Column(0)
    Keyword_I(Keyword_t.C9) = Me!EnvelopeInput_sfrm.Form.C9.Column(0)
    
    ' control inputs
    Keyword_I(Keyword_t.G1) = Me!ControlInput_sfrm.Form.G1.Column(0)
    Keyword_I(Keyword_t.G2) = Me!ControlInput_sfrm.Form.G2.Column(0)
    Keyword_I(Keyword_t.G3) = Me!ControlInput_sfrm.Form.G3.Column(0)
    Keyword_I(Keyword_t.G7) = Me!ControlInput_sfrm.Form.G7.Column(0)
    
    ' hvac inputs
    Keyword_I(Keyword_t.E1) = Me!HvacInput_sfrm.Form.E1.Column(0)
    Keyword_I(Keyword_t.E2) = Me!HvacInput_sfrm.Form.E2.Column(0)
    Keyword_I(Keyword_t.E3) = Me!HvacInput_sfrm.Form.E3.Column(0)
    Keyword_I(Keyword_t.E4) = Me!HvacInput_sfrm.Form.E4.Column(0)
    Keyword_I(Keyword_t.E5) = Me!HvacInput_sfrm.Form.E5.Column(0)
    Keyword_I(Keyword_t.E6) = Me!HvacInput_sfrm.Form.E6.Column(0)
    Keyword_I(Keyword_t.E7) = Me!HvacInput_sfrm.Form.E7.Column(0)
    Keyword_I(Keyword_t.F1) = Me!HvacInput_sfrm.Form.F1.Column(0)
    
    ' lighting inputs
    Keyword_I(Keyword_t.D1) = Me!LightingInput_sfrm.Form.D1.Column(0)
    Keyword_I(Keyword_t.D2) = Me!LightingInput_sfrm.Form.D2.Column(0)
    Keyword_I(Keyword_t.D3) = Me!LightingInput_sfrm.Form.D3.Column(0)
    Keyword_I(Keyword_t.D4) = Me!LightingInput_sfrm.Form.D4.Column(0)
    Keyword_I(Keyword_t.H2) = Me!LightingInput_sfrm.Form.H2.Column(0)
    
End Sub


Private Sub ConfirmButton_Click()

    ' initialize form
    initi
    
    ' get form inputs
    getInputs

    ' query baseline table first
    'queryBaselineTable

    ' query tables to get data
    queryTables

    ' process energy data
    processEnergyData
    
    ' unit conversion
    unitConversion
    
    ' output to form
    outputToForm

End Sub


' query baseline table
Private Sub queryBaselineTable()

    Dim condition As String
    condition = "ID = 1"
    
    ' query table
    queryTable condition, BaselineTableName

End Sub


' query all the table we have and store selection data
Private Sub queryTables()
  
'    queryTable selectedIndex(), tt_Roof
    Dim i As Long
    Dim j As Long
    Dim kw() As Long
    
    ' query until baseline
    For i = 1 To Table_T.tt_Baseline
      
        Select Case i

            Case Table_T.tt_Wall                    ' building envelope table keyword
                ReDim kw(1 To 6) As Long
                kw(1) = Keyword_t.C1
                kw(2) = Keyword_t.C2
                kw(3) = Keyword_t.C3
                kw(4) = Keyword_t.C6
                kw(5) = Keyword_t.C7
                kw(6) = Keyword_t.C9
                
            Case Table_T.tt_Roof                    ' roof table keyword
                ReDim kw(1 To 2) As Long
                kw(1) = Keyword_t.C4
                kw(2) = Keyword_t.C5
                
            Case Table_T.tt_IntEquipment     ' internal equipment table keyword
                ReDim kw(1 To 1) As Long
                kw(1) = Keyword_t.H2
            
            Case Table_T.tt_Refrigeration      ' refrigeration table keyword
                ReDim kw(1 To 1) As Long
                kw(1) = Keyword_t.F1
                
            Case Table_T.tt_ExtLighting         ' external lighting table keyword
                ReDim kw(1 To 1) As Long
                kw(1) = Keyword_t.D4
                
            Case Table_T.tt_IntLighting         ' internal lighting table keyword
                ReDim kw(1 To 8) As Long
                kw(1) = Keyword_t.C6
                kw(2) = Keyword_t.C7
                kw(3) = Keyword_t.C8
                kw(4) = Keyword_t.D1
                kw(5) = Keyword_t.D2
                kw(6) = Keyword_t.D3
                kw(7) = Keyword_t.G1
                kw(8) = Keyword_t.G2
                
            Case Table_T.tt_Hvac                     ' hvac table keyword
                ReDim kw(1 To 9) As Long
                kw(1) = Keyword_t.E1
                kw(2) = Keyword_t.E2
                kw(3) = Keyword_t.E3
                kw(4) = Keyword_t.E4
                kw(5) = Keyword_t.E5
                kw(6) = Keyword_t.E6
                kw(7) = Keyword_t.E7
                kw(8) = Keyword_t.G3
                kw(9) = Keyword_t.G7
                               
            Case Table_T.tt_Baseline                 ' baseline table keyword


            Case Else
                Exit Sub

        End Select
        
        ' using kw as SQL condition
        ' we make sure all the keyword condition is equal to selected input condition
        Dim kwStr As String
    
        ' for baseline case
        If i = Table_T.tt_Baseline Then
            kwStr = "ID = 1"
        ' for any case which we have keyword in the table
        Else
            If UBound(kw) < 1 Then
                Exit Sub
            End If
            kwStr = Keyword_S(kw(1)) & " = " & Keyword_I(kw(1))
            For j = 2 To UBound(kw)
                kwStr = kwStr & " and " & Keyword_S(kw(j)) & " = " & Keyword_I(kw(j))
            Next j
        End If
        
        queryTable kwStr, i
    
    Next i
                    
End Sub


' general table query function input query condition and table name
' the query output will store results in corresponding arrays
Private Sub queryTable(condition As String, table As Table_T)

    Dim db As Database
    Dim Lrs As DAO.Recordset
    Dim LSQL, energyStr As String
    Dim i As Long

    ' assign table for query
    Dim tableName As String
    tableName = Table_Name(table)

    energyStr = "ID, " & Table_Header(1)
    For i = 2 To Energy_Cat_T.ect_Num
        energyStr = energyStr & ", " & Table_Header(i)
    Next i

    'Open connection to current Access database
    Set db = CurrentDb()

    'Create SQL statement to retrieve value from an input result table
    LSQL = "select " & energyStr & " from " & Table_Name(table) & " where " & condition

    'MsgBox LSQL
    Dim test_str As String
    
    Set Lrs = db.OpenRecordset(LSQL)

    'Retrieve value if data is found, and store in current table type row for each energy value
    If Lrs.EOF = False Then
        test_str = Lrs("ID") & "; "
        For i = 1 To Energy_Cat_T.ect_Num
            If Table_Name(table) = BaselineTableName Then
                EnergyBaseline(i) = Lrs(Table_Header(i))
            ' handle other table type
            Else
                EnergyTable(table, i) = Lrs(Table_Header(i))
            End If
            test_str = test_str & Lrs(Table_Header(i)) & "; "
        Next i
    Else
            LGST = "Not found"
    End If

    'MsgBox (condition & vbCr & vbCr _
    '             & test_str)
                
    Lrs.Close
    Set Lrs = Nothing

End Sub


' process all energy output data and ready for output
Private Sub processEnergyData()

    ' process HVAC energy output

    Dim scales(1 To Energy_Cat_T.ect_Hvac_Num) As Double            ' hvac energy scale
    Dim netEnergy(1 To Energy_Cat_T.ect_Hvac_Num) As Double     ' the Num net energy change compared with hvac baseline

    Dim i, j As Long

    ' store previous energy data
    For i = Energy_Cat_T.ect_Heating To Energy_Cat_T.ect_Num
        EnergyOutput_Previous(i) = EnergyOutput(i)
    Next i

    ' store previous total electricity and gas energy
    For i = 1 To Energy_T.et_Num
        TotalEnergyOutput_Previous(i) = TotalEnergyOutput(i)
    Next i

    ' loop through each hvac energy for those table which is related to internal load change.
    ' this includes, Envelope, Roof, IntLight, IntEquip.
    For i = 1 To Table_T.tt_IntEquipment
        For j = Energy_Cat_T.ect_Heating To Energy_Cat_T.ect_Hvac_Num
            netEnergy(j) = netEnergy(j) + (EnergyTable(i, j) - EnergyBaseline(j))             ' accumulate netEnergy for each energyTable compared with baseline energy
        Next j
    Next i

    ' get energy scale for each hvac energy type
    For i = Energy_Cat_T.ect_Heating To Energy_Cat_T.ect_Hvac_Num
        scales(i) = netEnergy(i) / EnergyBaseline(i)
    Next i

    ' scale the final energy output based on hvac energy selection
    For i = Energy_Cat_T.ect_Heating To Energy_Cat_T.ect_Hvac_Num
        EnergyOutput(i) = (1 + scales(i)) * EnergyTable(Table_T.tt_Hvac, i)
    Next i

    ' process other non-hvac energy output
    EnergyOutput(Energy_Cat_T.ect_ExtLighting) = EnergyTable(Table_T.tt_ExtLighting, Energy_Cat_T.ect_ExtLighting)
    EnergyOutput(Energy_Cat_T.ect_IntLighting) = EnergyTable(Table_T.tt_IntLighting, Energy_Cat_T.ect_IntLighting)
    EnergyOutput(Energy_Cat_T.ect_IntEquipmentEle) = EnergyTable(Table_T.tt_IntEquipment, Energy_Cat_T.ect_IntEquipmentEle)
    EnergyOutput(Energy_Cat_T.ect_IntEquipmentGas) = EnergyTable(Table_T.tt_IntEquipment, Energy_Cat_T.ect_IntEquipmentGas)
    EnergyOutput(Energy_Cat_T.ect_Refrigeration) = EnergyTable(Table_T.tt_Refrigeration, Energy_Cat_T.ect_Refrigeration)

    ' clear total energy array
    For i = 1 To Energy_T.et_Num
        TotalEnergyOutput(i) = 0
    Next i
    
    ' sum up total electricity and gas energy
    For i = 1 To Energy_Cat_T.ect_Num
        TotalEnergyOutput(EnergyType(i)) = TotalEnergyOutput(EnergyType(i)) + EnergyOutput(i)
    Next i
    
End Sub


' convert energy output
' we use [KWH] for electricity and [J] for gas
Private Sub unitConversion()

    Dim i As Integer

    For i = 1 To Energy_Cat_T.ect_Num
        If EnergyType(i) = Energy_T.et_Electricity Then
            EnergyOutput(i) = EnergyOutput(i) / JtoKwh / 1000
        Else
            EnergyOutput(i) = EnergyOutput(i) / 1000000000#
        End If
    Next i

    TotalEnergyOutput(Energy_T.et_Electricity) = TotalEnergyOutput(Energy_T.et_Electricity) / JtoKwh / 1000
    TotalEnergyOutput(Energy_T.et_Gas) = TotalEnergyOutput(Energy_T.et_Gas) / 1000000000#

End Sub


' output processed energy results to form
Private Sub outputToForm()

    Dim i As Integer
        
    For i = 1 To Energy_Cat_T.ect_Num
      
        Me!ResultOutput_sfrm.Form.Controls(Table_Header(i)) = EnergyOutput(i)   ' output each energy to form
        
    Next i
    
    ' output total energy to form
    For i = 1 To Energy_T.et_Num
        Me!TotalResultOutput_sfrm.Form.Controls(EnergyType_S(i)) = TotalEnergyOutput(i)
    Next i
    

    ' there is some data in previous results, we can compare results
    If EnergyOutput_Previous(1) > 0 Then
        For i = 1 To Energy_Cat_T.ect_Num
            Me!SimpleResultOutput_sfrm.Form.Controls(Table_Header(i)) = EnergyOutput(i) - EnergyOutput_Previous(i)
        Next i
        
        For i = 1 To Energy_T.et_Num
            Me!TotalResultOutput_sfrm.Form.Controls(EnergyType_S(i) & "Change") = TotalEnergyOutput(i) - TotalEnergyOutput_Previous(i)
        Next i
    End If


End Sub
